﻿Imports Microsoft.VisualBasic
Imports System.Reflection
Imports System.Data
Imports System.Data.SqlClient

Public Class CDManager

#Region "Setup"

    ''' <summary>
    ''' Specify the name of the connection string to the database
    ''' </summary>
    ''' <remarks></remarks>
    Private _DatabaseConnectionName As String = ConfigurationManager.AppSettings("DatabaseConnectionName")
    Public ReadOnly Property DatabaseConnectionName() As String
        Get
            Return _DatabaseConnectionName
        End Get
    End Property

    ''' <summary>
    ''' Definition of the source virtual path for the generation of classes
    ''' </summary>
    ''' <remarks></remarks>
    Private _ClassSourcePath As String = ConfigurationManager.AppSettings("ClassSourcePath")
    Public ReadOnly Property ClassSourcePath() As String
        Get
            Return _ClassSourcePath
        End Get
    End Property

    ''' <summary>
    ''' Defining the virtual path recipient of the generation of classes
    ''' </summary>
    ''' <remarks></remarks>
    Private _ClassDestinationPath As String = ConfigurationManager.AppSettings("ClassDestinationPath")
    Public ReadOnly Property ClassDestinationPath() As String
        Get
            Return _ClassDestinationPath
        End Get
    End Property

#End Region

#Region "Declarations"

    ''' <summary>
    ''' Defining the class containing information on the primary key
    ''' </summary>
    ''' <remarks></remarks>
    Private Class PrimaryKeyInfo
        Public Text As String = String.Empty
        Public Value As Integer = 0
    End Class

    ''' <summary>
    ''' Defining the class on Information on the table name and primary key
    ''' </summary>
    ''' <remarks></remarks>
    Private Class DataInfo
        Public TableName As String = String.Empty
        Public PrimaryKey As New PrimaryKeyInfo
    End Class

    ''' <summary>
    ''' Retrieves the field identified as the primary key of a table
    ''' </summary>
    ''' <param name="tName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetPrimaryKey(ByVal tName As String) As String
        Try
            Dim Pk As String = String.Empty
            Dim Sql As String = "SELECT Column_Name AS 'PrimaryKey' FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS cu "
            Sql &= "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc ON cu.Constraint_Name=tc.Constraint_Name "
            Sql &= "WHERE cu.Table_Name=@TableName AND tc.Constraint_Type='Primary Key'"
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    cm.Parameters.AddWithValue("@TableName", tName)
                    If Not cm.ExecuteScalar Is DBNull.Value Then Pk = cm.ExecuteScalar
                End Using
                cn.Close()
                cn.Dispose()
            End Using
            Return Pk
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Inserts a property in a class, returns a string
    ''' </summary>
    ''' <param name="pName"></param>
    ''' <param name="pType"></param>
    ''' <param name="pDefault"></param>
    ''' <param name="IsReadOnly"></param>
    ''' <returns>Returns a string containing the code of the property to be included</returns>
    ''' <remarks></remarks>
    Protected Function InsertProperty(ByVal pName As String, ByVal pType As String, ByVal pDefault As String, Optional ByVal IsReadOnly As Boolean = False) As String
        Try
            If pDefault.Length > 0 Then pDefault = GetDefaultValueOfField(pType, pDefault)
            Dim s As String = vbTab & "Private _" & pName & " As " & pType
            If Not String.IsNullOrEmpty(pDefault) Then s &= " = " & pDefault
            s &= vbNewLine
            s &= vbTab & "Public "
            If IsReadOnly Then s &= "ReadOnly "
            s &= "Property " & pName & " As " & pType & vbNewLine
            s &= vbTab & vbTab & "Get" & vbNewLine
            s &= vbTab & vbTab & vbTab & "Return _" & pName & vbNewLine
            s &= vbTab & vbTab & "End Get" & vbNewLine
            If Not IsReadOnly Then
                s &= vbTab & vbTab & "Set(value As " & pType & ")" & vbNewLine
                s &= vbTab & vbTab & vbTab & "_" & pName & " = value" & vbNewLine
                s &= vbTab & vbTab & "End Set" & vbNewLine
            End If
            s &= vbTab & "End Property" & vbNewLine
            Return s
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Returns the default format in vb starting from the default value in sql format
    ''' </summary>
    ''' <param name="FieldType"></param>
    ''' <param name="FieldValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetDefaultValueOfField(ByVal FieldType As String, ByVal FieldValue As String) As String
        Try
            Dim sReturn As String = FieldValue
            Select Case FieldValue.ToUpper
                Case "GETDATE"
                    sReturn = "Datetime.Now"
                Case "0" And FieldType.ToUpper = "BIT"
                    sReturn = "False"
                Case "1" And FieldType.ToUpper = "BIT"
                    sReturn = "True"
            End Select
            If GetTypeOfField(FieldType) = "String" Then sReturn = """" & sReturn & """"
            Return sReturn
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Returns the field type in vb format starting from the type of field format in sql / vb
    ''' </summary>
    ''' <param name="FieldType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetTypeOfField(ByVal FieldType As String) As String
        Try
            Dim sReturn As String = String.Empty
            Select Case FieldType.ToUpper
                Case "INT", "INTEGER"
                    sReturn = "Integer"
                Case "BIT", "BOOLEAN"
                    sReturn = "Boolean"
                Case "DATETIME"
                    sReturn = "Datetime"
                Case Else
                    sReturn = "String"
            End Select
            Return sReturn
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Move a class generated by the Public folder (default) in the App_Code (default)
    ''' </summary>
    ''' <param name="ClassName"></param>
    ''' <param name="SourceDir"></param>
    ''' <param name="DestinationDir"></param>
    ''' <remarks></remarks>
    Protected Sub ClassMove(ByVal ClassName As String, Optional ByVal SourceDir As String = Nothing, Optional DestinationDir As String = Nothing)
        Try
            If SourceDir Is Nothing Then SourceDir = ConfigurationManager.AppSettings("ClassSourcePath")
            If DestinationDir Is Nothing Then DestinationDir = ConfigurationManager.AppSettings("ClassDestinationPath")
            Dim comArgs As String = "/c "
            comArgs &= "move " & HttpContext.Current.Server.MapPath(SourceDir) & "\" & ClassName & " "
            comArgs &= HttpContext.Current.Server.MapPath(DestinationDir)
            Dim procStartInfo As New System.Diagnostics.ProcessStartInfo("cmd", comArgs)
            procStartInfo.UseShellExecute = False
            procStartInfo.CreateNoWindow = True
            procStartInfo.RedirectStandardOutput = True
            Dim proc As New System.Diagnostics.Process
            proc.StartInfo = procStartInfo
            proc.Start()
            proc.WaitForExit()
        Catch ex As Exception
            Throw
        End Try
    End Sub

#End Region

#Region "ClassMethods"

    ''' <summary>
    ''' Retrieve table name and primary key of a class
    ''' </summary>
    ''' <param name="Obj">Class object</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function Initialize(ByVal Obj As Object) As DataInfo
        Try
            Dim r As New DataInfo
            For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                If Not _Property.CanWrite Then
                    If _Property.Name = "TableName" Then r.TableName = _Property.GetValue(Obj, Nothing)
                    If _Property.Name = "PrimaryKey" Then r.PrimaryKey.Text = _Property.GetValue(Obj, Nothing)
                End If
            Next
            Return r
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Performs a read data from the database based on the primary key
    ''' </summary>
    ''' <param name="Obj">Class object</param>
    ''' <remarks></remarks>
    Public Sub Read(ByVal Obj As Object)
        Try
            Dim di As DataInfo = Initialize(Obj)
            Dim Sql As String = "SELECT "
            For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                If _Property.CanWrite Then
                    If _Property.Name = di.PrimaryKey.Text Then di.PrimaryKey.Value = _Property.GetValue(Obj, Nothing)
                    Sql &= _Property.Name & ", "
                End If
            Next
            Sql = Left(Sql, Sql.Length - 2)
            Sql &= " FROM " & di.TableName
            Sql &= " WHERE " & di.PrimaryKey.Text & " = @" & di.PrimaryKey.Text
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    cm.Parameters.AddWithValue("@" & di.PrimaryKey.Text, di.PrimaryKey.Value)
                    Using dr As SqlDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            dr.Read()
                            For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                                If _Property.CanWrite Then
                                    If Not dr(_Property.Name) Is DBNull.Value Then _Property.SetValue(Obj, dr(_Property.Name), Nothing)
                                End If
                            Next
                        End If
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Save the data in the class. Insert if the primary key is empty, otherwise update
    ''' </summary>
    ''' <param name="Obj">Class object</param>
    ''' <remarks></remarks>
    Public Sub Save(ByVal Obj As Object)
        Try
            Dim di As DataInfo = Initialize(Obj)
            For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                If _Property.CanWrite And _Property.Name = di.PrimaryKey.Text Then di.PrimaryKey.Value = _Property.GetValue(Obj, Nothing)
            Next
            Dim Sql As String = String.Empty
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    cm.CommandType = Data.CommandType.Text
                    cm.Parameters.Clear()
                    Dim _FieldCount As Integer = 0
                    If di.PrimaryKey.Value = 0 Then
                        Sql = "INSERT INTO " & di.TableName & " ("
                        For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                            If _Property.CanWrite And _Property.Name <> di.PrimaryKey.Text Then
                                _FieldCount += 1
                                Sql &= _Property.Name & ", "
                            End If
                        Next
                        Sql = Left(Sql, Sql.Length - 2)
                        Sql &= ") VALUES ("
                        For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                            If _Property.CanWrite And _Property.Name <> di.PrimaryKey.Text Then
                                Sql &= "@" & _Property.Name & ", "
                                If _Property.GetValue(Obj, Nothing) Is Nothing Then
                                    cm.Parameters.AddWithValue("@" & _Property.Name, DBNull.Value)
                                Else
                                    If _Property.PropertyType Is GetType(System.DateTime) Then
                                        If _Property.GetValue(Obj, Nothing) = System.Data.SqlTypes.SqlDateTime.MinValue.Value Then
                                            cm.Parameters.AddWithValue("@" & _Property.Name, DBNull.Value)
                                        Else
                                            cm.Parameters.AddWithValue("@" & _Property.Name, _Property.GetValue(Obj, Nothing))
                                        End If
                                    Else
                                        cm.Parameters.AddWithValue("@" & _Property.Name, _Property.GetValue(Obj, Nothing))
                                    End If
                                End If
                            End If
                        Next
                        Sql = Left(Sql, Sql.Length - 2)
                        Sql &= ") SELECT @@IDENTITY AS 'LastPrimaryKey'"
                    Else
                        Sql = "UPDATE " & di.TableName & " SET "
                        cm.Parameters.AddWithValue("@" & di.PrimaryKey.Text, di.PrimaryKey.Value)
                        For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                            If _Property.CanWrite And _Property.Name <> di.PrimaryKey.Text Then
                                _FieldCount += 1
                                Sql &= _Property.Name & " = @" & _Property.Name & ", "
                                If _Property.GetValue(Obj, Nothing) Is Nothing Then
                                    cm.Parameters.AddWithValue("@" & _Property.Name, DBNull.Value)
                                Else
                                    If _Property.PropertyType Is GetType(System.DateTime) Then
                                        If _Property.GetValue(Obj, Nothing) = System.Data.SqlTypes.SqlDateTime.MinValue.Value Then
                                            cm.Parameters.AddWithValue("@" & _Property.Name, DBNull.Value)
                                        Else
                                            cm.Parameters.AddWithValue("@" & _Property.Name, _Property.GetValue(Obj, Nothing))
                                        End If
                                    Else
                                        cm.Parameters.AddWithValue("@" & _Property.Name, _Property.GetValue(Obj, Nothing))
                                    End If
                                End If
                            End If
                        Next
                        Sql = Left(Sql, Sql.Length - 2)
                        Sql &= " WHERE " & di.PrimaryKey.Text & " = @" & di.PrimaryKey.Text
                    End If
                    cm.CommandText = Sql
                    If di.PrimaryKey.Value = 0 Then
                        For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                            If _Property.Name = di.PrimaryKey.Text Then
                                _Property.SetValue(Obj, Int32.Parse(cm.ExecuteScalar), Nothing)
                            End If
                        Next
                    Else
                        If _FieldCount > 0 Then cm.ExecuteNonQuery()
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Performs a general reading on the table, optionally with filter parameters and possible grouping
    ''' </summary>
    ''' <param name="Obj">Class object</param>
    ''' <param name="Params">Any condition or filter parameters</param>
    ''' <param name="IsDistinct">If TRUE performs a grouping of data</param>
    ''' <param name="Top">Any subset of data</param>
    ''' <param name="FieldList"></param>
    ''' <returns>Returns a dataset</returns>
    ''' <remarks></remarks>
    Public Function List(ByVal Obj As Object, Optional Params() As SqlParameter = Nothing, Optional IsDistinct As Boolean = False, Optional ByVal Top As Integer = 0, Optional ByVal FieldList As ArrayList = Nothing) As DataSet
        Try
            Dim r As New DataSet
            Dim di As DataInfo = Initialize(Obj)
            Dim Sql As String = "SELECT "
            If IsDistinct Then Sql &= "DISTINCT "
            If Top > 0 Then Sql &= "TOP " & Top & " "
            For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                If _Property.CanWrite Then
                    If _Property.Name = di.PrimaryKey.Text Then di.PrimaryKey.Value = _Property.GetValue(Obj, Nothing)
                    If Not FieldList Is Nothing Then
                        For Each field As String In FieldList
                            If field = _Property.Name Then Sql &= _Property.Name & ", "
                        Next
                    Else
                        Sql &= _Property.Name & ", "
                    End If
                End If
            Next
            Sql = Left(Sql, Sql.Length - 2)
            Sql &= " FROM " & di.TableName
            Sql &= " WHERE Deleted = 0 " '---Da personalizzare in base alle esigenze del progetto---
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    If Not Params Is Nothing Then
                        For Each p As SqlParameter In Params
                            Sql &= " AND "
                            If p.Value Is DBNull.Value Then
                                Sql &= p.ParameterName.Replace("@", "") & " IS NULL "
                            Else
                                Sql &= p.ParameterName.Replace("@", "") & " = " & p.ParameterName
                                cm.Parameters.AddWithValue(p.ParameterName, p.Value)
                            End If
                        Next
                    End If
                    Sql = Sql.Trim
                    cm.CommandText = Sql
                    Using da As New SqlDataAdapter(cm)
                        da.Fill(r)
                    End Using
                End Using
            End Using
            Return r
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Cancellation logical / physical of a record of a class based on the value of the primary key
    ''' </summary>
    ''' <param name="Obj">Class object</param>
    ''' <param name="PhysicalDelete">If true, perform a physical deletion of record</param>
    ''' <remarks></remarks>
    Public Sub Delete(ByVal Obj As Object, Optional ByVal PhysicalDelete As Boolean = False)
        Try
            Dim di As DataInfo = Initialize(Obj)
            Dim Sql As String
            If Not PhysicalDelete Then
                Sql = "UPDATE " & di.TableName
                Sql &= " SET Deleted = 1 " '---Da personalizzare in base alle esigenze del progetto---
            Else
                Sql = "DELETE FROM " & di.TableName
            End If
            Sql &= " WHERE " & di.PrimaryKey.Text & " = @" & di.PrimaryKey.Text
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    For Each _Property As PropertyInfo In Obj.GetType.GetProperties
                        If _Property.CanWrite Then
                            If _Property.Name = di.PrimaryKey.Text Then
                                cm.Parameters.AddWithValue("@" & _Property.Name, _Property.GetValue(Obj, Nothing))
                            End If
                        End If
                    Next
                    cm.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>Generates a class</summary>
    ''' <param name="TableName">Table name</param>
    ''' <param name="IsReadOnly">If true, all properties of the class will be set to read-only</param>
    ''' <param name="SourceDir">Path creation of the class (requires write permissions)</param>
    ''' <param name="DestinationDir">Destination path of the class (default: / App_Code)</param>
    ''' <remarks>Generate an entire class to use in the project from a given table exists in the database</remarks>
    Public Sub Generate(ByVal TableName As String, Optional ByVal IsReadOnly As Boolean = False, Optional ByVal SourceDir As String = "", Optional DestinationDir As String = "")
        Try
            If String.IsNullOrEmpty(SourceDir) Then SourceDir = _ClassSourcePath
            If String.IsNullOrEmpty(DestinationDir) Then DestinationDir = _ClassDestinationPath
            Dim Sql As String = "SELECT "
            Sql &= "co.Column_Name AS 'Name'"
            Sql &= ",co.Data_Type AS 'Type'"
            Sql &= ",ISNULL(co.Character_Maximum_Length, 0) AS 'Lenght'"
            Sql &= ",CASE WHEN co.Data_Type='Bit' AND NOT co.Column_Default IS NULL THEN "
            Sql &= "CASE WHEN REPLACE(REPLACE(REPLACE(ISNULL(co.Column_Default,''),'(',''),')',''),'''','') = 0 THEN 'False' ELSE 'True' END ELSE "
            Sql &= "CASE WHEN cu.Column_Name IS NULL THEN REPLACE(REPLACE(REPLACE(ISNULL(COLUMN_DEFAULT,''),'(',''),')',''),'''','') ELSE '0' END END AS 'DEFAULT'"
            Sql &= ",cu.Column_Name AS 'PrimaryKey' "
            Sql &= "FROM INFORMATION_SCHEMA.COLUMNS AS co "
            Sql &= "LEFT JOIN (INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS cu "
            Sql &= "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc ON cu.Constraint_Name = tc.Constraint_Name) ON co.Column_Name = cu.Column_Name AND cu.Table_Name = @TableName "
            Sql &= "WHERE co.Table_Name = @TableName "
            Sql &= "ORDER BY co.Ordinal_Position ASC"
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    cm.Parameters.AddWithValue("@TableName", TableName)
                    Using dr As SqlDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            Dim ClassName As String = TableName & ".vb"
                            Dim sw As IO.StreamWriter = IO.File.CreateText(HttpContext.Current.Server.MapPath("~" & SourceDir) & "/" & ClassName)
                            sw.WriteLine("Imports Microsoft.VisualBasic" & vbNewLine)
                            sw.WriteLine("Public Class " & TableName & vbNewLine)
                            sw.WriteLine(InsertProperty("TableName", "String", TableName, True))
                            While dr.Read
                                If Not dr("PrimaryKey") Is DBNull.Value Then
                                    sw.WriteLine(InsertProperty("PrimaryKey", "String", dr("PrimaryKey"), True))
                                End If
                                sw.WriteLine(InsertProperty(dr("Name"), GetTypeOfField(dr("Type")), dr("Default"), IsReadOnly))
                            End While
                            sw.Write("End Class")
                            sw.Close()
                            ClassMove(ClassName, SourceDir, DestinationDir)
                        End If
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>Generates all classes</summary>
    ''' <param name="ViewsToo">If TRUE also generates views</param>
    ''' <param name="SourceDir"></param>
    ''' <param name="DestinationDir"></param>
    ''' <remarks>Generates a class for each table in the database</remarks>
    Public Sub GenerateAll(Optional ViewsToo As Boolean = False, Optional ByVal SourceDir As String = "", Optional DestinationDir As String = "")
        Try
            If String.IsNullOrEmpty(SourceDir) Then SourceDir = _ClassSourcePath
            If String.IsNullOrEmpty(DestinationDir) Then DestinationDir = _ClassDestinationPath
            Dim Sql As String = "SELECT Table_Name,Table_Type FROM INFORMATION_SCHEMA.Tables "
            If Not ViewsToo Then
                Sql &= "WHERE Table_Type NOT IN ('VIEW') "
            End If
            Sql &= "ORDER BY Table_Name ASC"
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Using cm As New SqlCommand(Sql, cn)
                    Using dr As SqlDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                        If dr.HasRows Then
                            While dr.Read
                                Generate(dr("Table_Name"), IIf(dr("Table_Type") = "VIEW", True, False), SourceDir, DestinationDir)
                            End While
                        End If
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub

#End Region

#Region "DatabaseMethods"

    ''' <summary>
    ''' Verifies if a table exists
    ''' </summary>
    ''' <param name="TableName"></param>
    ''' <returns>Return TRUE if table exists</returns>
    ''' <remarks></remarks>
    Public Function ExistTable(ByVal TableName As String) As Boolean
        Try
            Dim r As Boolean = False
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Dim Sql As String = "SELECT COUNT(*) AS 'Qta' FROM Information_Schema.Tables "
                Sql &= "WHERE Table_Type='BASE TABLE' AND Table_Name=@Table_Name"
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    cm.Parameters.AddWithValue("@Table_name", TableName)
                    If Int32.Parse(cm.ExecuteScalar) > 0 Then r = True
                End Using
            End Using
            Return r
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Verifies if a view exists
    ''' </summary>
    ''' <param name="ViewName"></param>
    ''' <returns>Return TRUE if view exists</returns>
    ''' <remarks></remarks>
    Public Function ExistView(ByVal ViewName As String) As Boolean
        Try
            Dim r As Boolean = False
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Dim Sql As String = "SELECT COUNT(*) AS 'Qta' FROM Information_Schema.Tables "
                Sql &= "WHERE Table_Type='VIEW' AND Table_Name=@Table_Name"
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    cm.Parameters.AddWithValue("@Table_Name", ViewName)
                    If Int32.Parse(cm.ExecuteScalar) > 0 Then r = True
                End Using
            End Using
            Return r
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Delete a table
    ''' </summary>
    ''' <param name="TableName"></param>
    ''' <param name="ClassFilePath"></param>
    ''' <remarks>Delete a table and relative class</remarks>
    Public Sub DropTable(ByVal TableName As String, Optional ByVal ClassFilePath As String = "")
        Try
            If String.IsNullOrEmpty(ClassFilePath) Then ClassFilePath = _ClassDestinationPath
            If ExistTable(TableName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "DROP TABLE " & TableName
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
                IO.File.Delete(HttpContext.Current.Server.MapPath("~" & ClassFilePath) & "/" & TableName & ".vb")
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Delete a view
    ''' </summary>
    ''' <param name="ViewName"></param>
    ''' <param name="ClassFilePath"></param>
    ''' <remarks>Delete a view and relative class</remarks>
    Public Sub DropView(ByVal ViewName As String, Optional ByVal ClassFilePath As String = "")
        Try
            If String.IsNullOrEmpty(ClassFilePath) Then ClassFilePath = _ClassDestinationPath
            If ExistView(ViewName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "DROP VIEW " & ViewName
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
                IO.File.Delete(HttpContext.Current.Server.MapPath("~" & ClassFilePath) & "/" & ViewName & ".vb")
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Creates a table
    ''' </summary>
    ''' <param name="TableName"></param>
    ''' <param name="PrimaryKey"></param>
    ''' <param name="WithDrop"></param>
    ''' <param name="SourceClassFilePath"></param>
    ''' <param name="DestinationClassFilePath"></param>
    ''' <remarks>Creates a table with only primary key and relative class into project</remarks>
    Public Sub CreateTable(ByVal TableName As String, ByVal PrimaryKey As String, Optional WithDrop As Boolean = False, Optional SourceClassFilePath As String = "", Optional ByVal DestinationClassFilePath As String = "")
        Try
            If String.IsNullOrEmpty(SourceClassFilePath) Then SourceClassFilePath = _ClassSourcePath
            If String.IsNullOrEmpty(DestinationClassFilePath) Then DestinationClassFilePath = _ClassDestinationPath
            If WithDrop Then
                If ExistTable(TableName) Then DropTable(TableName)
            End If
            If Not ExistTable(TableName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "CREATE TABLE [" & TableName & "] ("
                    Sql &= "[" & PrimaryKey & "] [INT] IDENTITY(1,1) NOT NULL,"
                    Sql &= "CONSTRAINT [" & PrimaryKey & "_" & TableName & "] PRIMARY KEY CLUSTERED "
                    Sql &= "([" & PrimaryKey & "] ASC))"
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
                Generate(TableName, SourceClassFilePath, DestinationClassFilePath)
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Creates a view
    ''' </summary>
    ''' <param name="ViewName">Name of view</param>
    ''' <param name="ViewSql">Sql query for view creation</param>
    ''' <remarks>Creates a view based on the query passed as a parameter</remarks>
    Public Sub CreateView(ByVal ViewName As String, ByVal ViewSql As String)
        Try
            If Not ExistView(ViewName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "CREATE VIEW " & ViewName & " AS "
                    Sql &= ViewSql & " GO"
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Verifies if a column in a table exists
    ''' </summary>
    ''' <param name="ColumnName"></param>
    ''' <param name="TableName"></param>
    ''' <returns>Return TRUE if the column exists</returns>
    ''' <remarks></remarks>
    Public Function ExistColumn(ByVal ColumnName As String, ByVal TableName As String) As Boolean
        Try
            Dim r As Boolean = False
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Dim Sql As String = "SELECT COUNT(*) AS 'Qta' FROM Information_Schema.Columns "
                Sql &= "WHERE Table_Name=@Table_Name AND Column_Name=@Column_Name"
                Using cm As New SqlCommand(Sql, cn)
                    cm.Parameters.Clear()
                    cm.Parameters.AddWithValue("@Table_Name", TableName)
                    cm.Parameters.AddWithValue("@Column_Name", ColumnName)
                    If Int32.Parse(cm.ExecuteScalar) > 0 Then r = True
                End Using
            End Using
            Return r
        Catch ex As Exception
            Throw
        End Try
    End Function

    ''' <summary>
    ''' Delete a column in a table
    ''' </summary>
    ''' <param name="ColumnName">Column name to delete</param>
    ''' <param name="TableName">Table name for column delete</param>
    ''' <remarks></remarks>
    Public Sub DeleteColumn(ByVal ColumnName As String, ByVal TableName As String)
        Try
            If ExistColumn(ColumnName, TableName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "ALTER TABLE " & TableName
                    Sql &= " DROP COLUMN " & ColumnName
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Add a column in a table
    ''' </summary>
    ''' <param name="ColumnName"></param>
    ''' <param name="ColumnType"></param>
    ''' <param name="ColumnLength"></param>
    ''' <param name="TableName"></param>
    ''' <remarks></remarks>
    Public Sub CreateColumn(ByVal ColumnName As String, ByVal ColumnType As String, ByVal ColumnLength As Integer, ByVal TableName As String)
        Try
            If Not ExistColumn(ColumnName, TableName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "ALTER TABLE " & TableName
                    Sql &= " ADD " & ColumnName & " " & ColumnType
                    If ColumnLength > 0 Then
                        Sql &= " (" & ColumnLength & ")"
                    End If
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Alter a column in a table
    ''' </summary>
    ''' <param name="ColumnName"></param>
    ''' <param name="ColumnType"></param>
    ''' <param name="ColumnLength"></param>
    ''' <param name="TableName"></param>
    ''' <remarks></remarks>
    Public Sub AlterColumn(ByVal ColumnName As String, ByVal ColumnType As String, ByVal ColumnLength As Integer, ByVal TableName As String)
        Try
            If ExistColumn(ColumnName, TableName) Then
                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                    cn.Open()
                    Dim Sql As String = "ALTER TABLE " & TableName
                    Sql &= " ALTER COLUMN " & ColumnName & " " & ColumnType
                    If ColumnLength > 0 Then
                        Sql &= " (" & ColumnLength & ")"
                    End If
                    Using cm As New SqlCommand(Sql, cn)
                        cm.ExecuteNonQuery()
                    End Using
                End Using
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub

    ''' <summary>
    ''' Add / Edit a column in a table
    ''' </summary>
    ''' <param name="ColumnName"></param>
    ''' <param name="ColumnType"></param>
    ''' <param name="ColumnLength"></param>
    ''' <param name="TableName"></param>
    ''' <remarks></remarks>
    Public Sub SaveColumn(ByVal ColumnName As String, ByVal ColumnType As String, ByVal ColumnLength As Integer, ByVal TableName As String)
        Try
            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings(_DatabaseConnectionName).ConnectionString)
                cn.Open()
                Dim Sql As String = "ALTER TABLE " & TableName
                If ExistColumn(ColumnName, TableName) Then
                    Sql &= " ALTER COLUMN " & ColumnName & " " & ColumnType
                Else
                    Sql &= " ADD " & ColumnName & " " & ColumnType
                End If
                If ColumnLength > 0 Then
                    Sql &= " (" & ColumnLength & ")"
                End If
                Using cm As New SqlCommand(Sql, cn)
                    cm.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub

#End Region

End Class
